package globalCode;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

//A class handling the interface between the program-created SQL tables and the application itself

public class TableHandler {
	Connection con; //The connection to the SQL database
	boolean updateLocked = false; //A locking variable preventing concurrent access to the database
	//The array of tablehandlerthreads, used because there is only one TableHandler instance
	ArrayList<TableHandlerThread> thtArray = new ArrayList<TableHandlerThread>(); 

	public TableHandler(Connection c){
		con = c;
		//establishTables();
		try {
			con.createStatement().executeQuery("USE inetwork");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	//Function used to rebuild the tables in the database in the case of a reset or new install -- mainly will be left unused.
	@SuppressWarnings("unused")
	private void establishTables() {
		//Creates the statement to send info to the database
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		//Reads the file with the SQL queries to write to the database
		File file = new File("./src/globalCode/tablegen.sql");
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		//Stores the entire file as an array of bytes (characters)
		byte[] b = new byte[(int) file.length()];
		try {
			fis.read(b);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		//Builds a string out of the byte array, clearing spaces from each side
		String s = new String(b).trim();
		//Splits the byte array along the delimiter, ";"
		String[] splitString = s.split(";");
		//For every command in the array, send it to the database
		for(int i = 0; i<splitString.length; i++)
			try {
				stmt.executeUpdate(splitString[i].trim()+";");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

	}

	//Creates a threaded version and calculates the given group's connections
	public TableHandlerThread loadThreaded(TableHandlerContainer gcsTestApplet, int groupID){
		GroupConnections gcs = new GroupConnections(groupID, con); //The GroupConnections object to run the calculations
		TableHandlerThread gcst = new TableHandlerThread(gcsTestApplet, gcs, this); //The TableHandlerThread to actually run the calculation, allows for concurrency
		thtArray.add(gcst); //Adds the tablehandlerthread to this tablehandler'slist
		gcst.start(); //Starts the above thread
		return gcst; //Returns that thread once started
	}

	//Rebuilds the entire database: i.e., runs through every group and calculates their connections. Very time consuming.
	public TableHandlerThread[] rebuildDatabase(TableHandlerContainer thc){
		TableHandlerThread[] thtArray = new TableHandlerThread[0]; //Array of every thread analyzed
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("SELECT `id` FROM `group` ORDER BY `id`"); //Collects every group ID from the table
			rs.last();
			int lastIndex = rs.getRow();
			thtArray = new TableHandlerThread[lastIndex-1]; //The total number of groups
			thc.thtGroupsInSet(lastIndex); //Report this number to the container

			//Load the threaded calculation for every group, storying it in the array.
			for(int i = 1; i<lastIndex; i++){
				thtArray[i-1] = loadThreaded(thc, i);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return thtArray;

	}

	//Updates the group connections table in the database based in the inputted data
	public void update(int groupID, Object[][] info){
		//Prevents concurrent use of this function by multiple threads
		while(updateLocked);
		updateLocked = true;

		try{
			Statement stmt = con.createStatement();
			stmt.executeQuery("USE inetwork");
			/*for(int i = 0; i<info.length; i++){
				System.out.print("| ");
				for(int j = 0; j<8; j++){
					System.out.print(info[j]+" | ");
				}
				System.out.println();
			}*/

			//For each inputted user pair, update that pair's entry in the database.
			for(int i = 0; i<info.length; i++){
				//The first user should always have the smaller id
				int usrA = Math.min((Integer)info[i][0], (Integer)info[i][1]);
				int usrB = Math.max((Integer)info[i][0], (Integer)info[i][1]);
				//Execute the actual insert, updating if the pair already exists
				stmt.executeUpdate("INSERT INTO usr_connection (usr1, usr2, friends, wall_posts, page_comments, shared_groups, forum_posts, score) " +
						"VALUES ("+usrA+", "+usrB+", "+info[i][2]+", "+info[i][3]+", "+info[i][4]+", "+info[i][5]+", "+info[i][6]+", "+info[i][7]+") " +
						"ON DUPLICATE KEY UPDATE friends="+info[i][2]+", wall_posts="+info[i][3]+", " +
						"page_comments="+info[i][4]+", shared_groups="+info[i][5]+", forum_posts="+info[i][6]+", score="+info[i][7]+";");
				stmt.executeUpdate("INSERT INTO group_connection (usr1, usr2, groupID) VALUES " +
						"("+usrA+", "+usrB+", "+groupID+")" + "ON DUPLICATE KEY UPDATE groupID="+groupID+";");
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		updateLocked = false; //Unlocks this function
	}

	//Returns the short "score" array of the pairs in the given group ID -- does not do any actual updating, only pulling from the database
	public int[][] getShortArray(int groupID){
		Statement stmt;
		int[][] shortArray = new int[0][0];
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT usr_connection.usr1, usr_connection.usr2, usr_connection.score FROM usr_connection INNER JOIN " +
					"group_connection ON (usr_connection.usr1=group_connection.usr1 AND " +
					"usr_connection.usr2=group_connection.usr2) WHERE group_connection.groupID="+groupID+" ORDER BY usr_connection.score DESC");
			rs.last();
			shortArray = new int[rs.getRow()][3];
			rs.beforeFirst();
			int i = 0;
			while(rs.next()){
				shortArray[i][0] = rs.getInt(1);
				shortArray[i][1] = rs.getInt(2);
				shortArray[i][2] = rs.getInt(3);
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return shortArray;
	}

	//Updates user and group connections based on changes since the previous update
	public void runUpdates(){
		Statement stmt;
		
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT MAX(`max_read`) FROM `jconnection_read`");
			rs.first();
			Integer lastIndex = rs.getInt(1);
			
			if(lastIndex != null){
				rs = stmt.executeQuery("SELECT * FROM `jconnection_to_update` WHERE `id` > "+lastIndex);
				rs.last();
				Object[][] rsArray = new Object[rs.getRow()][5]; //Array storing all of the pairs/groups to be updated
				int i = 0;
				rs.beforeFirst();
				
				//Pulls out the information for each entry, placing it in rsArray
				while(rs.next()){
					for(int j = 0; j<5; j++){
						rsArray[i][j] = rs.getObject(j+1);
					}
					i++;
				}

				for(i = 0; i< rsArray.length; i++){ //For each entry in rsArray
					
					//If the entry is between two users
					if(rsArray[i][4].equals("user_to_user")){
						
						//Create and analyze the associated user connection
						UserConnection uc = new UserConnection((int)(long)(Long)rsArray[i][1], (int)(long)(Long)rsArray[i][2], con);
						uc.analyze();
						
						//Update the table with the information from this userconnection
						Object[] info = uc.toInfoArray();
						stmt.executeUpdate("INSERT INTO usr_connection (usr1, usr2, friends, wall_posts, page_comments, shared_groups, forum_posts, score) " +
								"VALUES ("+info[0]+", "+info[1]+", "+info[2]+", "+info[3]+", "+info[4]+", "+info[5]+", "+info[6]+", "+info[7]+") " +
								"ON DUPLICATE KEY UPDATE friends="+info[2]+", wall_posts="+info[3]+", " +
								"page_comments="+info[4]+", shared_groups="+info[5]+", forum_posts="+info[6]+", score="+info[7]+";");
					}
					
					//If the entry is between a user and their group
					else if(rsArray[i][4].equals("user_to_group")){
						try{
							//Setup an associated grouptouserconnections
							GroupToUserConnections gtuCon = new GroupToUserConnections((int)(long)(Long)rsArray[i][3], (int)(long)(Long)rsArray[i][1], con);
							Object [][] info = gtuCon.getFullRankings();
							
							//Update every user pair in the group with the inputed user, creating the connection if it didn't yet exist
							for(int j = 0; j<info.length; j++){
								int usrA = Math.min((Integer)info[j][0], (Integer)info[j][1]);
								int usrB = Math.max((Integer)info[j][0], (Integer)info[j][1]);
								stmt.executeUpdate("INSERT INTO usr_connection (usr1, usr2, friends, wall_posts, page_comments, shared_groups, forum_posts, score) " +
										"VALUES ("+usrA+", "+usrB+", "+info[j][2]+", "+info[j][3]+", "+info[j][4]+", "+info[j][5]+", "+info[j][6]+", "+info[j][7]+") " +
										"ON DUPLICATE KEY UPDATE friends="+info[j][2]+", wall_posts="+info[j][3]+", " +
										"page_comments="+info[j][4]+", shared_groups="+info[j][5]+", forum_posts="+info[j][6]+", score="+info[j][7]+";");
							}
						}catch (SQLException e) {
							e.printStackTrace();
						}
					}
					
					//If the entry is just a group as a whole (nonexistant at this point)
					else if(rsArray[i][4].equals("group")){
						try{
							
							//Create the associated group connection and analyze it
							GroupConnections gCon = new GroupConnections((Integer)rsArray[i][3], con);
							Object [][] info = gCon.getFullRankings();
							
							//Update every pair in the group, and the group itself
							for(int j = 0; j<info.length; j++){
								int usrA = Math.min((Integer)info[j][0], (Integer)info[j][1]);
								int usrB = Math.max((Integer)info[j][0], (Integer)info[j][1]);
								stmt.executeUpdate("INSERT INTO usr_connection (usr1, usr2, friends, wall_posts, page_comments, shared_groups, forum_posts, score) " +
										"VALUES ("+usrA+", "+usrB+", "+info[j][2]+", "+info[j][3]+", "+info[j][4]+", "+info[j][5]+", "+info[j][6]+", "+info[j][7]+") " +
										"ON DUPLICATE KEY UPDATE friends="+info[j][2]+", wall_posts="+info[j][3]+", " +
										"page_comments="+info[j][4]+", shared_groups="+info[j][5]+", forum_posts="+info[j][6]+", score="+info[j][7]+";");
								stmt.executeUpdate("INSERT INTO group_connection (usr1, usr2, groupID) VALUES " +
										"("+usrA+", "+usrB+", "+rsArray[i][3]+")" + "ON DUPLICATE KEY UPDATE groupID="+rsArray[i][3]+";");
							}
						}catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
				
				//Change the last index to be analyzed to match the new last index, updating this into the database
				int newMax = lastIndex;
				if(rsArray.length>0)
					newMax = (int)(long)(Long)rsArray[rsArray.length-1][0];
				stmt.executeUpdate("INSERT INTO `jconnection_read` (max_read) VALUES ("+newMax+");");
			}
			
			else{
				stmt.executeUpdate("INSERT INTO `jconnection_read` (max_read) VALUES ("+0+");");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	//Shutdown all threads in the process ASAP, clearing the associated array
	public void endAll() {
		for(TableHandlerThread tht: thtArray){
			tht.end();
		}
		thtArray.clear();
	}

	//Remove a specific thread from the array, making sure it's ended.
	public void removeThread(TableHandlerThread tht) {
		tht.end();
		thtArray.remove(tht);
	}
	
	//Retrieve the list of users in a specific group
	public int[] usersInGroup(int groupID){
		Statement stmt;
		ResultSet rs = null;
		int[] users = new int[0];
		
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT `member` FROM `group_member` WHERE `group`="+groupID);
			rs.last();
			int numUsers = rs.getRow();
			users = new int[numUsers];
			rs.first();
			//System.out.println("USERLIST");
			for(int i = 0; i<numUsers; i++){
				//System.out.println(rs.getInt("member"));
				users[i] = rs.getInt("member");
				rs.next();
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return users;
	}
	
	public boolean isTeacher(int userID){
		Statement stmt;
		ResultSet rs = null;
		String uname = "";
		try{
			stmt = con.createStatement();
			rs = stmt.executeQuery("Select `username` FROM `usr` WHERE `id`="+userID);
			rs.last();
			uname = rs.getString("username");
		} catch (SQLException e){
			e.printStackTrace();
		}
		return (uname.indexOf("@stu", 0)==-1);
	}

	public Integer getGroupContiv(int userID, int groupID) {
		int[][] userConList = getShortArray(groupID);
		int value = 0;
		for(int i = 0; i<userConList.length; i++){
			if(userConList[i][0]==userID || userConList[i][1]==userID)
				value+=userConList[i][2];
		}
		return value;
	}

	public String getName(int userID) {
		Statement stmt;
		ResultSet rs = null;
		String name = "";
		try{
			stmt = con.createStatement();
			rs = stmt.executeQuery("Select `firstname`, `lastname` FROM `usr` WHERE `id`="+userID);
			rs.last();
			name = rs.getString("firstname")+" "+rs.getString("lastname");
		} catch (SQLException e){
			e.printStackTrace();
		}
		return name;
	}

}
